﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace ExcelDataImporter
{
    public partial class ImportForm : Form
    {
        private List<string> csvColumns;
        private List<string> dbColumns;

        public ImportForm()
        {
            InitializeComponent();
        }

        private void ImportFormLoad(object sender, EventArgs e)
        {
            SheetNameLabel.Text = "Sheet Name: " + Session.Current.SelectedSheet;

            InitTableDropdownList();

            InitColumnMapping();
        }

        private void InitColumnMapping()
        {
            csvColumns = OleDbHelper.ReadSheetColumns(Session.Current.ExcelFile, Session.Current.SelectedSheet);
            
            foreach (string csvColumn in csvColumns)
            {
                ColumnMappingDataGridView.Rows.Add(csvColumn);
            }
        }

        private void InitTableDropdownList()
        {
            List<string> tables = SqlServerHelper.GetTables();
            tableComboBox.DataSource = tables;
        }

        private void TableComboBoxSelectedIndexChanged(object sender, EventArgs e)
        {
            if(tableComboBox.SelectedItem==null) return;

            dbColumns = SqlServerHelper.GetColumns(tableComboBox.SelectedItem.ToString());

            DataGridViewComboBoxColumn comboBoxColumn = (DataGridViewComboBoxColumn)ColumnMappingDataGridView.Columns[1];
            comboBoxColumn.DataSource = dbColumns;

            List<string> c1=new List<string>();
            List<string> c2=new List<string>();
            List<string> c3=new List<string>();
            List<string> c4=new List<string>();

            c1.AddRange(dbColumns);
            c2.AddRange(dbColumns);
            c3.AddRange(dbColumns);
            c4.AddRange(dbColumns);
            constComboBox1.DataSource = c1;
            constComboBox2.DataSource = c2;
            constComboBox3.DataSource = c3;
            constComboBox4.DataSource = c4;
        }

        private void GoButtonClick(object sender, EventArgs e)
        {
            Dictionary<string, string> autoColumnMapping = BuildAutoColumnMapping();
            Dictionary<string, string> constColumnMapping = BuildConstColumnMapping();

            string insertStart = BuildInsertStart(autoColumnMapping,constColumnMapping);

            DataTable dataTable= OleDbHelper.ReadSheetContent(Session.Current.ExcelFile, Session.Current.SelectedSheet,filterTextBox.Text);

            StreamWriter streamWriter=new StreamWriter(Application.StartupPath+"/"+ Session.Current.SelectedSheet+".sql",false,Encoding.UTF8);
            StringBuilder sb=new StringBuilder();
            foreach (DataRow dataRow in dataTable.Rows)
            {
                sb.Length = 0;
                sb.Append(insertStart);
                foreach (KeyValuePair<string, string> pair in autoColumnMapping)
                {
                    sb.AppendFormat("'{0}',", ExcapeString(dataRow[pair.Key].ToString()));
                }
                foreach (KeyValuePair<string, string> pair in constColumnMapping)
                {
                    sb.AppendFormat("{0},", pair.Value);
                }
                sb.Length--;
                sb.Append(")\r\n");

                streamWriter.Write(sb.ToString());
            }
            streamWriter.Close();

            MessageBox.Show("你Y的，已经导出完成。");
        }

        private string ExcapeString(string value)
        {
            if (string.IsNullOrEmpty(value)) return value;
            return value.Replace("'", "''");
        }

        private Dictionary<string, string> BuildAutoColumnMapping()
        {
            Dictionary<string, string> columnMapping = new Dictionary<string, string>();

            foreach (DataGridViewRow row in ColumnMappingDataGridView.Rows)
            {
                if (row.Cells[0].Value != null && row.Cells[1].Value != null)
                {
                    columnMapping.Add(row.Cells[0].Value.ToString(), row.Cells[1].Value.ToString());
                }
            }
            return columnMapping;
        }

        private Dictionary<string, string> BuildConstColumnMapping()
        {
            Dictionary<string, string> columnMapping = new Dictionary<string, string>();
            if (constComboBox1.SelectedItem != "") columnMapping.Add(constComboBox1.SelectedItem.ToString(),constTextBox1.Text.Trim() );
            if (constComboBox2.SelectedItem != "") columnMapping.Add(constComboBox2.SelectedItem.ToString(), constTextBox2.Text.Trim());
            if (constComboBox3.SelectedItem != "") columnMapping.Add(constComboBox3.SelectedItem.ToString(), constTextBox3.Text.Trim());
            if (constComboBox4.SelectedItem != "") columnMapping.Add(constComboBox4.SelectedItem.ToString(), constTextBox4.Text.Trim());
            return columnMapping;
        }

        private string BuildInsertStart(Dictionary<string, string> columnMapping1, Dictionary<string, string> columnMapping2)
        {
            StringBuilder  insertStartBuilder=new StringBuilder();
            insertStartBuilder.AppendFormat("Insert into {0} (",  tableComboBox.SelectedItem );
            foreach (KeyValuePair<string, string> pair in columnMapping1)
            {
                insertStartBuilder.AppendFormat("{0},",pair.Value);
            }
            foreach (KeyValuePair<string, string> pair in columnMapping2)
            {
                insertStartBuilder.AppendFormat("{0},", pair.Key);
            }
            insertStartBuilder.Length--;
            insertStartBuilder.Append(") values(");
            return insertStartBuilder.ToString();
        }
    }
}